# -*- coding: utf-8 -*-
##############################################################################
#
#    OpenERP, Open Source Management Solution
#    Copyright (C) 2004-2010 Tiny SPRL (<http://tiny.be>).
#
#    This program is free software: you can redistribute it and/or modify
#    it under the terms of the GNU Affero General Public License as
#    published by the Free Software Foundation, either version 3 of the
#    License, or (at your option) any later version.
#
#    This program is distributed in the hope that it will be useful,
#    but WITHOUT ANY WARRANTY; without even the implied warranty of
#    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#    GNU Affero General Public License for more details.
#
#    You should have received a copy of the GNU Affero General Public License
#    along with this program.  If not, see <http://www.gnu.org/licenses/>.
#
##############################################################################
import xlwt
import math
from .. import format_common
from openerp.tools.translate import _
import cStringIO
from openerp.osv import osv, fields
import os, time
import base64
from openerp.tools import float_round, float_is_zero, float_compare
import logging
from babel.numbers import format_decimal
_logger = logging.getLogger(__name__)
from xlwt import *

class inventory_balance_report(osv.osv_memory):
     
    _name = 'inventory.balance.report'
    _description = 'Inventory Balance Report'
    
    _columns = {
        
        'location_filter_selection': fields.selection([('all_vall','All'),('def','Default'),('input', 'Input'),('selection','Selection')],'Location Filter Selection', required=True),
        'product_filter_selection': fields.selection([('all_vall','All'),('def','Default'),('input', 'Input'),('selection','Selection')],'Product Filter Selection', required=True),
        'location_ids' :fields.many2many('stock.location', 'report_stock_location_rel', 'report_id', 'location_id', 'Stock Location', domain=[('usage','=','internal'),('active','=',True)]),
        'product_ids' :fields.many2many('product.product', 'report_inventory_balance_product_product_rel', 'report_id', 'product_id', 'Product', domain=[('product_tmpl_id.type','=','product')]),
        'location_def_from':fields.many2one('stock.location', 'Location From',  domain=[('usage','=','internal'),('active','=',True)]),
        'location_def_to':fields.many2one('stock.location', 'Location To',  domain=[('usage','=','internal'),('active','=',True)]),
        'location_input_from':fields.char('Location From', size=128),
        'location_input_to':fields.char('Location To', size=128),
        'product_def_from':fields.many2one('product.product', 'Product From', required=False, domain=[('product_tmpl_id.type','=','product')]),
        'product_def_to':fields.many2one('product.product', 'Product To',required=False, domain=[('product_tmpl_id.type','=','product')]),
        'product_input_from':fields.char('Product From', size=128),
        'product_input_to':fields.char('Product To', size=128), 
        'sortby_ids': fields.one2many('inventory.balance.sortby','sortby_id', 'Sort By'),
    }
    
    _defaults = { 
        'location_filter_selection': 'all_vall',
        'product_filter_selection': 'all_vall',    
    }
    
    def onchange_filter_location_selection(self, cr, uid, ids, filter_selection, context=None):
        if context is None:
            context = {}
        res = {}
        if filter_selection != 'all_vall':
            
             res['value'] = {'location_def_from': False, 'location_def_to': False, 'location_input_from': False, 'location_input_to': False, 'location_ids': False,
                                }
        return res
    
    def onchange_filter_product_selection(self, cr, uid, ids, filter_selection, context=None):
        if context is None:
            context = {}
        res = {}
        if filter_selection != 'all_vall':
            
             res['value'] = {'product_def_from': False, 'product_def_to': False, 'product_input_from': False, 'product_input_to': False, 'product_ids': False,
                                }
        return res
      
    def create_vat(self, cr, uid, ids, context=None):
        if context is None:
            context = {}
        datas = {'ids': context.get('active_ids', [])}
        datas['model'] = 'inventory.balance.report'
        datas['form'] = self.read(cr, uid, ids)[0]
         
        return {
            'type': 'ir.actions.report.xml',
            'report_name': 'inventory.balance.report.pdf',
            'datas': datas,
        }  
     
    def _search_data(self, cr, uid, ids, data_obj, val_data, filter_selection, default_from, default_to, input_from, input_to, selection_ids, context=None):
        #Out Default Value
        data_header = ''
        data_ids = []
        #Declaration
 
        if context is None:
            context = {}
          
#         special_case = 'special_case' in context and context['special_case'] or False  
        if filter_selection == 'all_vall':
            data_ids = data_obj.search(cr, uid, val_data, order='id ASC')
        if filter_selection == 'def':
            data_found = False 
            if default_from and data_obj.browse(cr, uid, default_from) and data_obj.browse(cr, uid, default_from).name:
                default_from_header = data_obj.browse(cr, uid, default_from).name
                data_found = True
                val_data.append(('name', '>=', default_from_header)) 
            if default_to and data_obj.browse(cr, uid, default_to) and data_obj.browse(cr, uid, default_to).name:
                default_to_header = data_obj.browse(cr, uid, default_to).name
                data_found = True
                val_data.append(('name', '<=', default_to_header)) 
            if data_found:
                data_header = '"' + default_from_header + '" - "' + default_to_header + '"'
                data_ids = data_obj.search(cr, uid, val_data, order='id ASC') 
        if filter_selection == 'input':
            data_found = False 
            if input_from:
                data_ids_from = data_obj.search(cr, uid, [('name','=ilike',input_from + '%')], order="name", limit=1)
                if data_ids_from:
                    input_from_name = data_obj.browse(cr, uid, data_ids_from[0]).name 
                    data_found = True
                    val_data.append(('name', '>=', input_from_name))
            if input_to:    
                data_ids_to = data_obj.search(cr, uid, [('name','=ilike', input_to + '%')], order="name desc", limit=1)
                if data_ids_to:
                    input_to_name = data_obj.browse(cr, uid, data_ids_to[0]).name
                    data_found = True
                    val_data.append(('name', '<=', input_to_name))
            data_header = '"' + input_from + '" - "' + input_to + '"'
            if data_found:
                data_ids = data_obj.search(cr, uid, val_data, order='id ASC')
        if filter_selection == 'selection':
            if selection_ids:
                sel_ids = '' 
                for sel in  data_obj.browse(cr, uid, selection_ids):
                    sel_ids += ('"%s",')%(sel.name,) 
                data_header = '[' + sel_ids +']'
                data_ids =selection_ids
        return data_header, data_ids
 
    def _build_contexts(self, cr, uid, ids, data, context=None):
        if context is None:
            context = {}
           
         
        stock_location_obj     = self.pool.get('stock.location')
        product_product_obj = self.pool.get('product.product')
        product_template_obj = self.pool.get('product.template')
           
        result = {}
        result['location_filter_selection'] = data['form']['location_filter_selection']
        result['product_filter_selection'] = data['form']['product_filter_selection']  
        result['sortby_ids'] = data['form']['sortby_ids'] or False 
        result['output_product_header'], result['products_ids'] = self._search_data(cr, uid, ids, product_product_obj,
                                                                                      [],
                                                                                      data['form']['product_filter_selection'],
                                                                                      data['form']['product_def_from'] or False,
                                                                                      data['form']['product_def_to'] or False,
                                                                                      data['form']['product_input_from'] or False,
                                                                                      data['form']['product_input_to'] or False,
                                                                                      data['form']['product_ids'] or False,
                                                                                      context=context)
        
        result['output_location_header'], result['location_ids'] = self._search_data(cr, uid, ids, stock_location_obj,
                                                                                      [],
                                                                                      data['form']['location_filter_selection'],
                                                                                      data['form']['location_def_from'] or False,
                                                                                      data['form']['location_def_to'] or False,
                                                                                      data['form']['location_input_from'] or False,
                                                                                      data['form']['location_input_to'] or False,
                                                                                      data['form']['location_ids'] or False,
                                                                                      context=context)
        
        
     
        return result
             
      
    def print_report(self, cr, uid, ids, context=None):
        if context is None:
            context = {}
        data = {}
        data['ids'] = context.get('active_ids', [])
        data['model'] = context.get('active_model', 'ir.ui.menu')
        data['form'] = self.read(cr, uid, ids, ['location_filter_selection',\
                                                'product_filter_selection','location_ids',\
                                                'product_ids', \
                                                'location_def_from','location_def_to',\
                                                'location_input_from','location_input_to',\
                                                'product_def_from','product_def_to',\
                                                'product_input_from','product_input_to','sortby_ids'], context=context)[0]
        for field in ['location_filter_selection',\
                                                'product_filter_selection','location_ids',\
                                                'product_ids', \
                                                'location_def_from','location_def_to',\
                                                'location_input_from','location_input_to',\
                                                'product_def_from','product_def_to',\
                                                'product_input_from','product_input_to','sortby_ids']:
            if isinstance(data['form'][field], tuple):
                data['form'][field] = data['form'][field][0]
            
        used_context = self._build_contexts(cr, uid, ids, data, context=context)
 
        return self._get_tplines_xls(cr, uid, ids, used_context, context=context)
      
    def _get_tplines_xls(self, cr, uid, ids,data, context):
        form = data
  
        if not ids:
            ids = data['ids']
        if not ids:
            return []
          
         
        user_obj = self.pool.get('res.users')
        dp_obj = self.pool.get('decimal.precision')
        inventory_balance_sortby_obj = self.pool.get('inventory.balance.sortby')
        
        stock_quant_obj = self.pool.get('stock.quant')
        product_product_obj = self.pool.get('product.product')
        product_template_obj = self.pool.get('product.template')
        
        results1        = []
        result        = []
        balance_by_cur = {} 
          
        val_product = ''
        
        
        output_product_header = form['output_product_header']
        output_location_header = form['output_location_header']
        location_ids = form['location_ids']
        product_ids = form['products_ids']  
         
        workbook = xlwt.Workbook()
        M_header_tstyle = format_common.font_style(position='center', bold=1, border=3, fontos='black', font_height=300)
        report_tstyle = format_common.font_style(position='left', bold=1, fontos='black', font_height=190)
        report_tstyle1 = format_common.font_style(position='left', bold=1, fontos='black', font_height=180)
        header_tstyle = format_common.font_style(position='right', bold=1, fontos='black', font_height=180)
        header_tstyle_c = format_common.font_style(position='center', bold=1, border=1, fontos='black', font_height=160)
        header_tstyle_l = format_common.font_style(position='left', bold=1,border=1, fontos='black', font_height=180)
        header_tstyle_c_Top = format_common.font_style(position='center', bold=1, border=4, fontos='black', font_height=180)
        header_tstyle_c_Bottom = format_common.font_style(position='center', bold=1, border=3, fontos='black', font_height=180)
        header_tstyle_r = format_common.font_style(position='right', bold=1,  border=1,fontos='black', font_height=180)
        view_tstyle = format_common.font_style(position='left', fontos='black', font_height=180)
        view_tstyle_c = format_common.font_style(position='center', border=1, fontos='black', font_height=150)
        view_tstyle_l = format_common.font_style(position='left',border=1, fontos='black', font_height=150)
        view_tstyle_r = format_common.font_style(position='right', fontos='black', font_height=180)
        view_tstyle_r_b = format_common.font_style(position='right', bold=1, fontos='black', font_height=180)
        other_tstyle = format_common.font_style(position='left', fontos='black', font_height=180)
        other_tstyle_c = format_common.font_style(position='center', fontos='black', font_height=180)
        other_tstyle_r = format_common.font_style(position='right', fontos='black', font_height=180)
        text_tstyle = format_common.font_style(position='left', bold=1, border=1, fontos='black', font_height=180)
        text_tstyle1 = format_common.font_style(position='left', bold=1, fontos='black', font_height=220)
        text_tstyle2 = format_common.font_style(position='left', fontos='black', font_height=220)
        text_tstyle_c = format_common.font_style(position='center', bold=1, fontos='black', font_height=180)
        text_tstyle_l = format_common.font_style(position='left', bold=1, fontos='black', font_height=180)
        text_tstyle_r = format_common.font_style(position='right', bold=1, fontos='black', font_height=180)
        text_tstyle_r_closing = format_common.font_style(position='right', bold=1,border=7, fontos='black', font_height=180)
        text_tstyle_Top = format_common.font_style(position='left', bold=1, border=4, fontos='black', font_height=180)
        text_tstyle_Bottom = format_common.font_style(position='left', bold=1, border=3, fontos='black', font_height=180)
        text_tstyle_Top_r = format_common.font_style(position='right', bold=1, border=4, fontos='black', font_height=180)
        text_tstyle_Top_l = format_common.font_style(position='left', bold=1, border=4, fontos='black', font_height=180)
        text_tstyle_Top_r_r = format_common.font_style(position='right', bold=1, border=6, fontos='black', font_height=180)
        text_tstyle_Top_l_l = format_common.font_style(position='left', bold=1, border=6, fontos='black', font_height=180)
        text_tstyle_Bottom_r = format_common.font_style(position='right', bold=1, border=3, fontos='black', font_height=180)
        text_tstyle_TopBottom = format_common.font_style(position='left', bold=1, border=2, fontos='black', font_height=180)
        text_tstyle_TopBottom_r = format_common.font_style(position='right', bold=1, border=2, fontos='black', font_height=180)
         
         ## decimat format number
        dp_data = {}
        decimal_format = easyxf(num_format_str = '0.000000')
        dp_ids = dp_obj.search(cr, uid, [])
          
        for dp_id in dp_obj.browse(cr, uid, dp_ids, context=None) :
            dp_data[dp_id.name] = dp_id.digits
         
        #get data decimal precision by name 
        dp_account = dp_data['Account']
        dp_product_price = dp_data['Product Price']
        dp_product_uom = dp_data['Product Unit of Measure']
         
        #digit by account
        digits = int(math.pow(10, dp_account))
        digit_output = '#,##0.' + str(digits)[1:]
 
        currency_format = easyxf(num_format_str = digit_output)
         
        credit_limit_format = easyxf('font: bold 1; border: top thin')
        credit_limit_format.num_format_str = digit_output
         
        currency_ttl_format = easyxf('font: bold 1')
        currency_ttl_format.num_format_str = digit_output
         
        total_ttl_format_closing = easyxf('font: bold 1; border: top thick, bottom thick, left thick, right thick')
        total_ttl_format_closing.num_format_str = digit_output
 
        currency_ttl_format_tlrb_view = easyxf('border: top thin, bottom thin, left thin, right thin; font: name Verdana; font: height 150')
        currency_ttl_format_tlrb_view.num_format_str = digit_output
         
        currency_ttl_format_tlrb = easyxf('font: bold 1; border: top thick, bottom thick, left thick, right thick')
        currency_ttl_format_tlrb.num_format_str = digit_output
         
         
        #digit by unit price
        digits_up = int(math.pow(10, dp_product_price))
        digit_output_up = '#,##0.' + str(digits_up)[1:]
 
        unit_price_format = easyxf(num_format_str = digit_output_up)
         
        unit_price_ttl_format_tlrb_view = easyxf('border: top thin, bottom thin, left thin, right thin; font: name Verdana; font: height 150')
        unit_price_ttl_format_tlrb_view.num_format_str = digit_output_up
         
        #digit by unit of measure
        digits_uom = int(math.pow(10, dp_product_uom))
        digit_output_uom = '#,##0.' + str(digits_uom)[1:]
 
        unit_of_measure_format = easyxf(num_format_str = digit_output_uom)
         
        unit_of_measure_ttl_format_tlrb_view = easyxf('border: top thin, bottom thin, left thin, right thin; font:name Verdana; font: height 150')
        unit_of_measure_ttl_format_tlrb_view.num_format_str = digit_output_uom
        #### decimat format number
        
       
        name_file = 'Inventory Balance Report.xls'
        sheet = workbook.add_sheet('Inventory Report  XLS')
#             sheet.row(0).height = 350
          
        row=0
         
        company_name = user_obj.browse(cr, uid, uid).company_id.name
        sheet.write_merge(row, row, 0, 3, company_name, report_tstyle1)
         
        #header titile 
        row+=1
        sheet.write_merge(row, row, 0, 3, 'Inventory Balance Report', report_tstyle1) 
             
        #printed date
        user_ids = user_obj.search(cr, uid, [('id','=',uid)])
        if user_ids :
            tz = user_obj.browse(cr, uid, user_ids[0])
            tz_name = tz.tz
            user_name = tz.name
        os.environ['TZ'] = tz_name #set time zone secara otomatis tidak hard code
        time.tzset()
        row+=1
        sheet.write_merge(row, row, 0, 0, 'Printed date : ' + time.strftime("%Y/%m/%d") + '  Time : '+ time.strftime("%H:%M:%S"), report_tstyle1)
        sheet.write_merge(row, row, 1, 2, 'Printed by : ' +user_name , header_tstyle)
        
        #location and product type
        output_location_header_str = "All Internal Location"
        if 'location_filter_selection' in form :
            if form['location_filter_selection'] != 'all_vall':
                output_location_header_str = output_location_header
        row+=1
        sheet.write_merge(row, row, 0, 0, 'Location : ' + output_location_header_str, report_tstyle1)
           
        if 'product_filter_selection' in form :
            if form['product_filter_selection'] != 'all_vall':
                row += 1
#                 sheet.write(row, 0, '', report_tstyle1)
                sheet.write_merge(row, row, 0, 3, 'Product: ' + output_product_header, report_tstyle1)
       
         
         
        name_of_sort_by ='' 
        output_sort_name ='' 
        if form['sortby_ids']:
            inventory_balance_sortby_ids = inventory_balance_sortby_obj.search(cr, uid, [('id', 'in', form['sortby_ids'])], order='seq asc') 
            for sort_by in inventory_balance_sortby_obj.browse(cr, uid, inventory_balance_sortby_ids, context=None):
                if sort_by.sort_by == 'location_name' :
                    name_of_sort_by = 'Location '
                elif sort_by.sort_by == 'product_name' :  
                    name_of_sort_by = 'Product '  
                output_sort_name += ('"%s",')%(name_of_sort_by,)        
            row += 1
#             sheet.write(row, 0, 'Sort By : ', report_tstyle1)
            sheet.write_merge(row, row, 0, 3,'Sort By : '+output_sort_name, report_tstyle1)
                        
        #Header Name
           
        #Column Width
        sheet.col(0).width = 256 * 41
        sheet.col(1).width = 256 * 27
        sheet.col(2).width = 256 * 20
        sheet.col(3).width = 256 * 10
        
        if product_ids:
            product_ids_qry = (product_ids and ((len(product_ids) == 1 and " and sq.product_id = " + str(product_ids[0]) + " ") or " and sq.product_id in " + str(tuple(product_ids)) + " ")) or " and sq.product_id in (0) " 
            val_product += product_ids_qry
              
        if location_ids:
            location_ids_qry = (location_ids and ((len(location_ids) == 1 and " and sq.location_id = " + str(location_ids[0]) + " ") or " and sq.location_id in " + str(tuple(location_ids)) + " ")) or " and sq.location_id in (0) "
            val_product += location_ids_qry    
         
#         val_product.append(('location_id.usage','=','internal'))  
        inventory_balance_sortby_ids = []
        if 'sortby_ids' in form:
            inventory_balance_sortby_ids = inventory_balance_sortby_obj.search(cr, uid, [('id', 'in', form['sortby_ids'])], order='seq desc')
          
#         stock_quant_ids=stock_quant_obj.search(cr, uid,val_product)
        
        cr.execute("select pt.name as product, sl.name as location, coalesce(sum(qty),0) as qty,pu.name as uom" \
                    " from stock_quant sq "\
                    "left join product_product pp on pp.id = sq.product_id "\
                    "left join product_template pt on pt.id = pp.product_tmpl_id "\
                    "left join stock_location sl on sq.location_id = sl.id "\
                    "left join product_uom pu on pu.id = pt.uom_id "\
                    "where " \
                    " sl.usage = 'internal' " \
                    + val_product + \
                    " group by pt.name, sl.name,pu.name  order by pt.name, sl.name")
                
        qry3 = cr.dictfetchall()
        if qry3:
            for t in qry3:
                results1.append({
                    'product_name' : t['product'],        
                    'qty_available' : t['qty'],
                    'location_name' : t['location'],
                    'uom' : t['uom'],
                      
                })
             
        if inventory_balance_sortby_ids:
            for inv_balance_sortby in inventory_balance_sortby_obj.browse(cr, uid, inventory_balance_sortby_ids):
                results1 = results1 and sorted(results1, key=lambda val_res: val_res[inv_balance_sortby.sort_by]) or []
        else:
            results1 = results1 and sorted(results1, key=lambda val_res: val_res['location_name']) or [] 
            results1 = results1 and sorted(results1, key=lambda val_res: val_res['product_name']) or [] 
            
           
        row += 1 
        sheet.write_merge(row, row, 0, 3, '', report_tstyle1)
             
        row+=1
#             sheet.row(row).height = 400
        sheet.write(row, 0, 'Description', header_tstyle_c)
        sheet.write(row, 1, 'Location', header_tstyle_c)
        sheet.write(row, 2, 'Quantity', header_tstyle_c)
        sheet.write(row, 3, 'UOM', header_tstyle_c) 
             
         
        for rs1 in results1:
            row += 1 
            sheet.write(row, 0, rs1['product_name'], view_tstyle_l)
            sheet.write(row, 1, rs1['location_name'], view_tstyle_l)
            sheet.write(row, 2, rs1['qty_available'], unit_of_measure_ttl_format_tlrb_view)
            sheet.write(row, 3, rs1['uom'], view_tstyle_l) 
                 
        row += 1 
        sheet.write_merge(row, row, 0, 3, '', report_tstyle1)
          
        row += 1
        sheet.write_merge(row, row, 0, 3, 'End of Report', text_tstyle_Top_l) 
         
        stream = cStringIO.StringIO()
        workbook.save(stream) 
         
        cr.execute(""" DELETE FROM output""")
         
        attach_id = self.pool.get('output').create(cr, uid, {'name': name_file, 'xls_output':base64.encodestring(stream.getvalue())})
        return {
                'name': _('Notification'),
                'context': context,
                'view_type': 'form',
                'view_mode': 'form',
                'res_model': 'output',
                'res_id':attach_id,
                'type': 'ir.actions.act_window',
                'target':'new'
                }
          
class inventory_balance_sortby(osv.osv_memory):
    
    _name = "inventory.balance.sortby"
    
    _columns = {
        'sortby_id': fields.many2one('inventory.balance.report', 'Sort By', required=True, ondelete='cascade'),
        'seq': fields.integer('Sequence', required=True),
        'sort_by': fields.selection([('location_name','Location'),('product_name', 'Product')],'Sort by', required=True),
    }     
    
inventory_balance_report()
# vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4:
